package vip.xiaonuo.smzq.modular.kcl.mapper;

import com.github.yulichang.base.MPJBaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import vip.xiaonuo.smzq.modular.kcl.entity.Sjyj;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;

/**
 * 矿山数据预警 Mapper
 *
 * @author sqq
 * @since 2024-06-04
 */
@Mapper
public interface SjyjMapper extends MPJBaseMapper<Sjyj> {
@Select("SELECT count(*) FROM kcl_sjyj where ${sql}")
    long getCount(@Param(value = "sql")String sql);

//校验今年资源量/上年度资源量>150%
@Select("\t\tSELECT \n" +
        "       CASE\n" +
        "           WHEN SUM(CASE WHEN zycllb = 19001 THEN jsl ELSE 0 END) > 0 THEN\n" +
        "               (SUM(CASE WHEN zycllb = 19001 THEN jsl ELSE 0 END) - SUM(CASE WHEN zycllb = 19012 THEN jsl ELSE 0 END)) /\n" +
        "               NULLIF(SUM(CASE WHEN zycllb = 19012 THEN jsl ELSE 0 END), 0)\n" +
        "           WHEN SUM(CASE WHEN zycllb = 19001 THEN ksl ELSE 0 END) > 0 THEN\n" +
        "               (SUM(CASE WHEN zycllb = 19001 THEN ksl ELSE 0 END) - SUM(CASE WHEN zycllb = 19012 THEN ksl ELSE 0 END)) /\n" +
        "               NULLIF(SUM(CASE WHEN zycllb = 19012 THEN ksl ELSE 0 END), 0)\n" +
        "           ELSE\n" +
        "               NULL\n" +
        "       END AS bilin\n" +
        "FROM kcl_zycl\n" +
        "WHERE kqbh = #{kqbh} AND nd = #{nd} AND djflbh = #{djflbh} AND kcdm = #{kc} and zycllx in ('TM','KZ','TD')")
    Double getBili(@Param("kqbh") String kqbh, @Param("nd") Integer nd, @Param("djflbh") Integer djflbh, @Param("kc") Integer kc);



    @Select("SELECT \n" +
            "    CASE\n" +
            "        WHEN SUM(CASE WHEN zycllb = 19002 THEN jsl ELSE 0 END) > 0 THEN\n" +
            "            (SUM(CASE WHEN zycllb = 19002 THEN jsl ELSE 0 END)-SUM(CASE WHEN zycllb = 19013 THEN jsl ELSE 0 END)) /\n" +
            "            NULLIF(SUM(CASE WHEN zycllb = 19013 THEN jsl ELSE 0 END), 0)\n" +
            "        WHEN SUM(CASE WHEN zycllb = 19002 THEN ksl ELSE 0 END) > 0 THEN\n" +
            "            (SUM(CASE WHEN zycllb = 19002 THEN ksl ELSE 0 END)-SUM(CASE WHEN zycllb = 19013 THEN ksl ELSE 0 END)) /\n" +
            "           NULLIF(SUM(CASE WHEN zycllb = 19013 THEN ksl ELSE 0 END), 0)\n" +
            "        ELSE\n" +
            "            NULL \n" +
            "    END AS bili\n" +
            "FROM kcl_zycl\n" +
            "WHERE kqbh = #{kqbh} AND nd = #{nd} AND djflbh = #{djflbh} AND kcdm = #{kc} and zycllx in ('TM','KZ','TD')")
    Double getLjbili(@Param("kqbh") String kqbh, @Param("nd") Integer nd, @Param("djflbh") Integer djflbh, @Param("kc") Integer kc);




    @Select("SELECT \n" +
            "    CASE\n" +
            "        WHEN SUM(CASE WHEN zycllb = 19001 THEN jsl ELSE 0 END) > 0 THEN\n" +
            "            SUM(CASE WHEN zycllb = 19001 THEN jsl ELSE 0 END) \n" +
            "        WHEN SUM(CASE WHEN zycllb = 19001 THEN ksl ELSE 0 END) > 0 THEN" +
            "            SUM(CASE WHEN zycllb = 19001 THEN ksl ELSE 0 END) \n" +
            "        ELSE\n" +
            "            NULL \n" +
            "    END AS zyl\n" +
            "FROM kcl_zycl\n" +
            "WHERE kqbh = #{kqbh} AND nd = #{nd} AND djflbh = #{djflbh} AND kcdm = #{kc}")
    Double getZyl(String kqbh, Integer nd, Integer djflbh, Integer kc);




    @Select("WITH cl AS (\n" +
            "             select CASE\n" +
            "                    WHEN SUM(CASE WHEN zycllb = 19001 THEN jsl ELSE 0 END) > 0 THEN\n" +
            "                        SUM(CASE WHEN zycllb = 19001 THEN jsl ELSE 0 END) \n" +
            "                    WHEN SUM(CASE WHEN zycllb = 19001 THEN ksl ELSE 0 END) > 0 THEN\n" +
            "                        SUM(CASE WHEN zycllb = 19001 THEN ksl ELSE 0 END) \n" +
            "                    ELSE\n" +
            "                        NULL \n" +
            "                END AS byl\n" +
            "    FROM kcl_zycl\n" +
            "    WHERE kqbh = #{kqbh} AND nd = #{nd} AND djflbh = #{djflbh} AND kcdm = #{kc} AND zycllx IN ('ZS', 'KX')\n" +
            "),\n" +
            "   zyl AS (\n" +
            "             select CASE\n" +
            "                    WHEN SUM(CASE WHEN zycllb = 19001 THEN jsl ELSE 0 END) > 0 THEN\n" +
            "                        SUM(CASE WHEN zycllb = 19001 THEN jsl ELSE 0 END) \n" +
            "                    WHEN SUM(CASE WHEN zycllb = 19001 THEN ksl ELSE 0 END) > 0 THEN\n" +
            "                        SUM(CASE WHEN zycllb = 19001 THEN ksl ELSE 0 END) \n" +
            "                    ELSE\n" +
            "                        NULL \n" +
            "                END AS byl\n" +
            "    FROM kcl_zycl\n" +
            "    WHERE kqbh = #{kqbh} AND nd = #{nd} AND djflbh = #{djflbh} AND kcdm = #{kc} AND zycllx IN ('TM', 'KZ', 'TD')\n" +
            ")\n" +
            "SELECT\n" +
            "       ROUND(COALESCE(cl.byl, 0.0)::numeric(10, 2) / COALESCE(zyl.byl, 1.0)::numeric(10, 2), 2) AS ksyj\n" +
            "\tFROM cl, zyl")
    Double getClbh(String kqbh, Integer nd, Integer djflbh, Integer kc);





}
